/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;

/**
 *
 * @author tamara
 */
public class CocktailTable {

    private DatabaseConnection db = new DatabaseConnection();
    private ArrayList<Cocktail> cocktails = new ArrayList<Cocktail>();
    private Connection connection;
    private Statement statement;
    private ResultSet rs;

    public void establishConnection() {
        db.connect();
    }

    public void cancelConnection() {
        db.disconnect();
    }

    public Cocktail newCocktail(String cocktailname) {
        Cocktail cocktail = null;
        try {
            this.establishConnection();
            connection = db.getCon();
            statement = connection.createStatement();
            statement.execute("SELECT MAX(CocktailID) FROM Cocktail");
            rs = statement.getResultSet();

            if (rs != null) {
                while (rs.next()) {
                    cocktail = new Cocktail(rs.getInt(1) + 1, cocktailname);
                    System.out.println("new:  " + cocktail.getCocktailID() + " " + cocktail.getCocktailname());
                }
            }

            statement = connection.createStatement();
            statement.execute("INSERT INTO Cocktail (CocktailID, CocktailName) VALUES (" + cocktail.getCocktailID() + ",'" + cocktail.getCocktailname() + "')");

            connection.commit();

            this.cancelConnection();
        } catch (SQLException ex) {
            Logger.getLogger(CocktailTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return cocktail;
    }

    public ArrayList<Cocktail> getCocktails() {
        try {
            this.establishConnection();
            connection = db.getCon();
            statement = connection.createStatement();
            statement.execute("SELECT cocktailID, cocktailName FROM Cocktail ORDER BY cocktailID ASC");
            rs = statement.getResultSet();

            Cocktail cocktail;

            if (rs != null) {
                while (rs.next()) {
                    cocktail = new Cocktail(rs.getInt(1), rs.getString(2));
                    cocktails.add(cocktail);
                }
            }
            this.cancelConnection();

        } catch (SQLException ex) {
            Logger.getLogger(CocktailTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return cocktails;
    }
}